package semantic_network;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.Set;
import java.util.HashSet;
import sql_handler.SQL_Handler;

public class SQL_Handler_Network extends SQL_Handler
{
    /*
     * This class provides functionality to retrieve, process, and store the data about resources
     * and descriptors from BMC_2009 and BMC_2009_2 databases.
     * Its functionality is dependant on the existance_of/access_to the required databases.
     * The dump files for databases are also provided.
     * @author: Hammad Afzal
     * @company: National University of Sciences and Technology, Pakistan
     */

    int count = 0;

    private Vector<String> descriptors = new Vector();
    private Vector descriptors_freq = new Vector();

    private Set descriptors1 = new HashSet();
    private Set descriptors2 = new HashSet();
    private Set resource_descriptors = new HashSet();

    Vector<String> class_values = new Vector();

    Vector d_sen_ids = new Vector();
    Vector descriptor_sen_ids = new Vector();
    Vector<String> service_ngrams;

    Vector_Manipulation vector_builder = new Vector_Manipulation();


    public SQL_Handler_Network()
    {
    }


    /*
     * It returns a vector, that contains all the descriptors that appear in given distinct sentence id.
     * The descriptor's length should be more than 2 characters.
     * The descriptors should also appear in the resource list of descriptors (of given bioinformatics resources)
     * or their n-grams.
     * The descriptors should not be one of the stop words, e.g. "e.g.", "Empty" etc.
     * @param d_sen_ids It is a vector that contains a distinct sentence ids 
     */

    public Vector get_descriptors_in_sentences(Vector d_sen_ids)
    {
        String sentence_ids_list = vector_builder.vector_to_string_for_making_db_query(d_sen_ids);

        descriptors = new Vector();
        descriptors_freq = new Vector();
        try
        {
            statement = connection.createStatement();

            String query = "SELECT Filtered_Descriptor, count(Filtered_Descriptor) as freq "
                    + "FROM __sentence_descriptors where D_Sen_ID IN " + sentence_ids_list
                    + " and char_length(Filtered_Descriptor) > 3 and (Filtered_Descriptor IN (Select Filtered_Descriptor from "
                    + "___bioinformatics_service_descriptors) OR Filtered_Descriptor "
                    + "IN (Select NGram from ___bioinformatics_service_descriptors_ngrams))and Filtered_Descriptor NOT IN "
                    + "('e.g.','EMPTY','Thi','EMPTY') group by Filtered_Descriptor";


            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptors.add(rs.getString("Filtered_Descriptor"));
                descriptors_freq.add(rs.getInt("freq"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Retrieving descriptors and their frequencies is : " + sqlex.getMessage());
        }
        return descriptors;
    }


    /**
     * Get frequency of appearance of Resource Name (Semantic Class Instance)
     * in given corpus (Data is used from the Occurrences table in BMC_2009 DB)
     * @param resource_name
     * @return
     */
    public int get_resource_name_freq(String resource_name)
    {
        resource_name = resource_name.trim();
        if (!(resource_name.equals("")))
        {
            try
            {
                statement = connection.createStatement();
                String query = "SELECT count(value) as Freq FROM occurrences where "
                        + "Value = '" + resource_name + "' group by Value";
                ResultSet rs = statement.executeQuery(query);
                while (rs.next())
                {
                    count = rs.getInt("Freq");
                }
                statement.close();
            }
            catch (SQLException sqlex)
            {
                System.out.println("SQL Exception . Find resource freq : " + sqlex.getMessage());
            }
        }
        else
        {
            System.out.println("Resource name is null. Please try again with valid value of resource");
            System.exit(0);

        }
        return count;
    }

    /**
     * Returns the list of distinct sen ids where the value of "Occurrence" appears
     * @param resource_name
     */
    public Vector get_distinct_sen_ids_for_given_resource_name(String resource_name)
    {
        Vector distinct_sen_ids = new Vector();
        resource_name = resource_name.trim();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT distinct sm.D_Sen_ID FROM Occurrences o, "
                    + "Sentence_Mapping sm where o.Value = '" + resource_name + "' and "
                    + "o.Sen_ID = sm.Sen_ID";
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                distinct_sen_ids.add(rs.getInt("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Retrieving distinct sen ids per class : " + sqlex.getMessage());
        }
        return distinct_sen_ids;
    }

    public Vector return_descriptors_freq()
    {
        return descriptors_freq;
    }

    public Vector return_descriptor_sen_ids()
    {
        return descriptor_sen_ids;
    }

    public Set return_descriptors1()
    {
        return descriptors1;
    }

    public Set return_descriptors2()
    {
        return descriptors2;
    }
}
